package biz.datalk.industrialland.admin.modules.online.config.util;

import biz.datalk.industrialland.admin.modules.online.config.cfg.enums.DBTypeEnum;
import biz.datalk.industrialland.admin.modules.online.config.exception.DBException;
import biz.datalk.industrialland.admin.modules.online.config.model.ColumnVo;
import biz.datalk.industrialland.common.def.enums.DBYNEnum;
import biz.datalk.industrialland.common.util.SpringContextUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author tarofang@163.com
 * @date 2019-12-20
 */
public class DbTableUtil {
    private static final Logger log = LoggerFactory.getLogger(DbTableUtil.class);

    private static DBTypeEnum dbType = null;


    public static Connection getConnection() throws SQLException {
        DataSource dataSource = SpringContextUtils.getApplicationContext().getBean(DataSource.class);
        return dataSource.getConnection();
    }

    public static DBTypeEnum getDatabaseType() throws SQLException {
        DataSource dataSource = SpringContextUtils.getApplicationContext().getBean(DataSource.class);
        return getDatabaseType(dataSource);
    }

    public static DBTypeEnum getDatabaseType(DataSource dataSource) throws SQLException {
        if (dbType != null) {
            return dbType;
        }
        Connection conn = null;
        String dbProductName = null;
        try {
            conn = dataSource.getConnection();
            dbType = getDatabaseType(conn);
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
            dbType = null;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        if (dbType == null) {
            throw new DBException(String.format("数据库类型:[%s]不识别!", dbProductName));
        }
        return dbType;
    }

    public static DBTypeEnum getDatabaseType(Connection conn) throws SQLException {
        if (dbType != null) {
            return dbType;
        }
        String dbProductName = null;
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            dbProductName = dbMetaData.getDatabaseProductName().toLowerCase();
            if (dbProductName.contains("mysql")) {
                dbType = DBTypeEnum.MYSQL;
            } else if (dbProductName.contains("oracle")) {
                dbType = DBTypeEnum.ORACLE;
            } else if (dbProductName.contains("postgresql")) {
                dbType = DBTypeEnum.POSTGRESQL;
            } else if (dbProductName.contains("sqlserver")) {
                dbType = DBTypeEnum.SQLSERVER;
            } else if (dbProductName.contains("sql server")) {
                dbType = DBTypeEnum.SQLSERVER;
            }
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
            dbType = null;
        }
        if (dbType == null) {
            throw new DBException(String.format("数据库类型:[%s]不识别!", dbProductName));
        }
        return dbType;
    }

    public static boolean judgeTableIsExit(String tableName) {
        if (StringUtils.isBlank(tableName)) {
            return false;
        }

        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            tableName = fixTableName(tableName, conn);
            DatabaseMetaData dbMetaData = conn.getMetaData();
            rs = dbMetaData.getTables(null, null, tableName, new String[]{"TABLE"});
            if (rs.next()) {
                // 表存在
                log.info("数据库表：【{}】已存在", tableName);
                return true;
            } else {
                // 表不存在
                return false;
            }
        } catch (SQLException ex) {
            throw new DBException(ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                log.warn(ex.getMessage(), ex);
            }
        }
    }

    private static String fixTableName(String tableName, Connection conn) throws SQLException {
        DBTypeEnum databaseType = getDatabaseType(conn);
        if (databaseType.equals(DBTypeEnum.ORACLE) || databaseType.equals(DBTypeEnum.POSTGRESQL)) {
            return tableName.toUpperCase();
        }
        return tableName;
    }

    public static String getDialect() throws SQLException {
        DBTypeEnum dbTypeEnum = getDatabaseType();
        return getDialect(dbTypeEnum);
    }

    private static String getDialect(DBTypeEnum dbTypeEnum) {
        if (dbTypeEnum == null) {
            throw new DBException("dbTypeEnum must not be null");
        }
        String dialect = "";
        switch (dbTypeEnum) {
            case MYSQL:
                dialect = "org.hibernate.dialect.MySQL5InnoDBDialect";
                break;
            case ORACLE:
                dialect = "org.hibernate.dialect.OracleDialect";
                break;
            case POSTGRESQL:
                dialect = "org.hibernate.dialect.PostgreSQLDialect";
                break;
            case SQLSERVER:
                dialect = "org.hibernate.dialect.SQLServerDialect";
                break;
            default:
                throw new DBException(String.format("Unknown dbtype: [%s]", dbTypeEnum.name()));
        }
        return dialect;
    }

    /**
     * 获取数据库中的所有表名
     *
     * @author tarofang@163.com
     * @date 2019年12月31日
     *
     * @return 表名
     */
    public static List<String> readDBAllTableName() throws SQLException {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            conn = getConnection();
            DBTypeEnum dbType = getDatabaseType(conn);

            String sql = "";
            switch (dbType) {
                case MYSQL:
                    sql = MessageFormat.format("SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = ''{0}''", conn.getCatalog());
                    break;
                case ORACLE:
                    sql = "SELECT DISTINCT colstable.table_name AS table_name FROM user_tab_cols colstable ORDER BY colstable.table_name";
                    break;
                case POSTGRESQL:
                    sql = "SELECT tablename AS table_name FROM pg_tables WHERE schemaname='public'";
                    break;
                case SQLSERVER:
                    sql = "SELECT DISTINCT c.name AS table_name FROM sys.objects c WHERE c.type = 'U'";
                    break;
                default:
                    break;
            }

            preparedStatement = conn.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<String> result = new ArrayList<>();
            while (resultSet.next()) {
                result.add(resultSet.getString(1));
            }

            return result;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }


    public static List<ColumnVo> getTableColumns(String tableName) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            conn = getConnection();
            DBTypeEnum dbType = getDatabaseType(conn);
            String dbName = conn.getCatalog();
            String sql = "";
            switch (dbType) {
                case MYSQL:
                    //sql = "SELECT column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable FROM information_schema.columns WHERE table_name = ''{0}'' AND table_schema = ''{1}''";
                    sql = "SELECT column_name,data_type,character_maximum_length,numeric_precision,numeric_scale,is_nullable,column_default,column_comment,ordinal_position FROM information_schema.COLUMNS WHERE table_name=''{0}'' AND table_schema=''{1}''";
                    sql = MessageFormat.format(sql, tableName, dbName);
                    break;
                // TODO 完善 ORACLE/PostgreSQL/SQLServer
                case ORACLE:
                case POSTGRESQL:
                case SQLSERVER:
                default:
                    break;
            }

            if (StringUtils.isBlank(sql)) {
                throw new DBException(String.format("Invalid DbType: [%s]", dbType));
            }

            log.info("exec SQL: [{}]", sql);
            preparedStatement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            resultSet = preparedStatement.executeQuery();
            // 移到最后一行
            resultSet.last();
            // 通过getRow方法得到当前行号，也就是记录数
            int rowCount = resultSet.getRow();
            List<ColumnVo> columnVoList = new ArrayList<>();
            if (rowCount <= 0) {
                // 该表不存在或者表中没有字段
                log.warn("The table [{}.{}] does not exist or there are no fields in the table.", dbName, tableName);
                return columnVoList;
            }
            // 将指针移动到第一行之前 【resultSet.first(); 将指针移动到第一行】
            resultSet.beforeFirst();
            while (resultSet.next()) {
                ColumnVo vo = new ColumnVo();
                // column_name
                vo.setFieldName(StringUtils.trimToEmpty(resultSet.getString(1)));
                // data_type
                vo.setFieldDataType(StringUtils.trimToEmpty(resultSet.getString(2)));
                // character_maximum_length
                vo.setCharMaxLength(StringUtils.trimToEmpty(resultSet.getString(3)));
                // numeric_precision
                vo.setPercision(StringUtils.trimToEmpty(resultSet.getString(4)));
                // numeric_scale
                vo.setScale(StringUtils.trimToEmpty(resultSet.getString(5)));
                // is_nullable
                vo.setFieldIsNull(convertNullAble(resultSet.getString(6)));
                // column_default
                vo.setFieldDefault(resultSet.getString(7));
                // column_comment
                vo.setFieldComment(resultSet.getString(8));
                // ordinal_position
                vo.setFieldOrder(resultSet.getString(9));
                vo.setFieldIsPk(DBYNEnum.N.name());

                vo.setFieldJavaType(convertDbType2JavaType(vo.getFieldDataType(), vo.getPercision(), vo.getScale()));


                log.debug("Table fieldName [{}.{}.{}].", dbName, tableName, vo.getFieldName());
                columnVoList.add(vo);
            }
            log.info("Table [{}.{}] field read successfully.", dbName, tableName);

            // 获取主键
            List<String> pks = getTablePks(tableName, conn, Boolean.FALSE);
            if (CollectionUtils.isNotEmpty(pks)) {
                for (String pk : pks) {
                    for (ColumnVo vo : columnVoList) {
                        if (StringUtils.equals(vo.getFieldName(), pk)) {
                            vo.setFieldIsPk("Y");
                            break;
                        }
                    }
                }
            }

            return columnVoList;
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
            return new ArrayList<>();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 数据库类型转java类型
     *
     * @author tarofang@163.com
     * @date 2020年01月19日
     *
     * @param fieldDbType 数据库类型
     * @param percision 整数部分长度
     * @param scale 小数部分长度
     * @return java 类型
     */
    public static String convertDbType2JavaType(String fieldDbType, String percision, String scale) {
        if (fieldDbType.contains("char")) {
            return "java.lang.String";
        }

        if (fieldDbType.contains("int")) {
            return "java.lang.Integer";
        }

        if (fieldDbType.contains("float")) {
            return "java.lang.Float";
        }

        if (fieldDbType.contains("double")) {
            return "java.lang.Double";
        }

        // ORACLE
        if (fieldDbType.contains("number")) {
            if (StringUtils.isNotBlank(scale) && Integer.parseInt(scale) > 0) {
                return "java.math.BigDecimal";
            } else if (StringUtils.isNotBlank(percision) && Integer.parseInt(percision) > 10) {
                return "java.lang.Long";
            } else {
                return "java.lang.Integer";
            }
        }

        if (fieldDbType.contains("decimal")) {
            return "java.math.BigDecimal";
        }

        if (fieldDbType.contains("date")) {
            return "java.util.Date";
        }

        if (fieldDbType.contains("time")) {
            return "java.util.Date";
        }

        if (fieldDbType.contains("blob")) {
            return "byte[]";
        }

        if (fieldDbType.contains("clob")) {
            return "java.sql.Clob";
        }

        if (fieldDbType.contains("numeric")) {
            return "java.math.BigDecimal";
        }

        return "java.lang.Object";
    }

    private static String convertNullAble(String nullAble) {
        nullAble = StringUtils.trimToEmpty(nullAble);
        if (StringUtils.equals(nullAble.toUpperCase(), "NO")) {
            return DBYNEnum.Y.name();
        }
        return DBYNEnum.N.name();
    }

    public static List<String> getTablePks(String tableName, Connection conn, boolean closeConnection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if (conn == null) {
                conn = getConnection();
            }
            DBTypeEnum dbType = getDatabaseType(conn);
            String dbName = conn.getCatalog();
            String sql = "";
            switch (dbType) {
                case MYSQL:
                    sql = "SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name=''{0}'' AND table_schema=''{1}'' AND constraint_name=''PRIMARY''";
                    sql = MessageFormat.format(sql, tableName, dbName);
                    break;
                // TODO 完善 ORACLE/PostgreSQL/SQLServer
                case ORACLE:
                case POSTGRESQL:
                case SQLSERVER:
                default:
                    break;
            }

            if (StringUtils.isBlank(sql)) {
                throw new DBException(String.format("Invalid DbType: [%s]", dbType));
            }

            log.info("exec SQL: [{}]", sql);
            preparedStatement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            resultSet = preparedStatement.executeQuery();

            List<String> pks = new ArrayList<>();
            while (resultSet.next()) {
                pks.add(resultSet.getString(1));
            }

            return pks;
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
            return new ArrayList<>();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (closeConnection) {
                    if (conn != null) {
                        conn.close();
                    }
                }
            } catch (SQLException ex) {
                log.error(ex.getMessage(), ex);
            }
        }
    }


    public static void main(String[] args) {
        try {

            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/datalk_core?characterEncoding=UTF-8&useUnicode=true&useSSL=false&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai";
            String username = "root";
            String pwd = "fangtailang";
            Connection connection = DriverManager.getConnection(url, username, pwd);

            //获取表列信息  ================== S
            //String tableName = "test_mytest_v1";
            String tableName = "demo_t_empty_test";
            //String sql = "SELECT column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable FROM information_schema.columns WHERE table_name = ''{0}'' AND table_schema = ''{1}''";
            String sql = "SELECT * FROM information_schema.columns WHERE table_name = ''{0}'' AND table_schema = ''{1}''";
            sql = MessageFormat.format(sql, tableName, connection.getCatalog());
            sql = "SELECT column_name AS '字段名',data_type AS '字段类型',character_maximum_length AS '字符类型长度',numeric_precision AS '数字整数部分长度',numeric_scale AS '数字小数部分长度',is_nullable AS '是否为null',column_default AS '默认值',column_comment AS '字段注释',ordinal_position AS '字段排序' FROM information_schema.COLUMNS WHERE table_name='" + tableName + "' AND table_schema='datalk_core';";
            System.out.println(sql);
            System.out.println("");
            PreparedStatement preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = preparedStatement.executeQuery();
            rs.last();
            System.out.println("rs.getRow() ==> " + rs.getRow());
            //rs.first();
            rs.beforeFirst();

            ResultSetMetaData m = rs.getMetaData();
            int columns = m.getColumnCount();
            System.out.println(columns);
            for (int i = 1; i <= columns; i++) {
                System.out.print(String.format("%s(%s)", m.getColumnName(i), m.getColumnLabel(i)));
                System.out.print("\t\t");
            }
            System.out.println("");
            while (rs.next()) {
                for (int i = 1; i <= columns; i++) {
                    System.out.print(rs.getString(i));
                    System.out.print("\t\t");
                }
                System.out.println();
            }

            List<String> pks = getTablePks(tableName, connection, Boolean.FALSE);
            System.out.println(pks);

            //DatabaseMetaData metaData = connection.getMetaData();
            //ResultSet rs = metaData.getTables(null, "%", tableName, new String[]{"TABLE"});
            //ResultSet rs = metaData.getColumns(null, "%", tableName, "%");
            //ResultSetMetaData m = rs.getMetaData();
            //int columns = m.getColumnCount();
            //for (int i = 1; i <= columns; i++) {
            //    System.out.print(m.getColumnName(i));
            //    System.out.print("\t\t");
            //}
            //System.out.println("");
            //while (rs.next()) {
            //    for (int i = 1; i <= columns; i++) {
            //        System.out.print(rs.getString(i));
            //        System.out.print("\t\t");
            //    }
            //    System.out.println();
            //    //System.out.println(rs.getString("REMARKS"));
            //}
            //获取表列信息  ================== E

            // 获取表名 ================== S
            //String sql = MessageFormat.format("SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema = ''{0}''", connection.getCatalog());
            //
            //PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //ResultSet rs = preparedStatement.executeQuery();
            //ResultSetMetaData m = rs.getMetaData();
            //int columns = m.getColumnCount();
            //// String table_name = rs.getString("table_name");
            //// System.out.println(table_name);
            //while (rs.next()) {
            //    for (int i = 1; i <= columns; i++) {
            //        System.out.print(rs.getString(i));
            //        System.out.print("\t\t");
            //    }
            //    System.out.println();
            //}
            // 获取表名 ================== E

            // String schema = connection.getSchema();
            //
            // Properties clientInfo = connection.getClientInfo();
            // String username1 = connection.getClientInfo("username");
            // DatabaseMetaData metaData = connection.getMetaData();
            //
            // String userName = metaData.getUserName();
            // ResultSet rs = metaData.getTables(catalog, null, null, new String[]{"TABLE"});
            // ResultSetMetaData m = rs.getMetaData();
            // int columns = m.getColumnCount();
            // for (int i = 1; i <= columns; i++) {
            //     System.out.print(m.getColumnName(i));
            //     System.out.print("\t\t");
            // }
            // System.out.println();
            // while (rs.next()) {
            //     for (int i = 1; i <= columns; i++) {
            //         System.out.print(rs.getString(i));
            //         System.out.print("\t\t");
            //     }
            //     System.out.println();
            // }

            connection.close();


            // // 73844866
            // System.out.println("MYSQL".hashCode());
            // // 73844866
            // System.out.println("ORACLE".hashCode());
            // // -1620389036
            // System.out.println("POSTGRESQL".hashCode());
            // // 912124529
            // System.out.println("SQLSERVER".hashCode());
            //
            // String dialect = getDialect();
            // System.out.println(dialect);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

}
